#===========================================================================
#
# postage_stamp_mysql.py
#
# Python script to query SWIRE_ES1 mysql databasea and create the postage
# stamp images of the radio contours overlayed onto the spitzer image.
#
#===========================================================================
#
# S. Weston
# AUT University
# May 2013
#===========================================================================

import array
import _mysql
import numpy
import math
import sys
import os

# ask which field to process
answer=raw_input('Which field ecdfs/elais ?')
print "\nentered : ",answer,"\n"

if answer == 'ecdfs': 
   survey='atlas'
   field='ecdfs'
   swire_schema='swire_cdfs'
   radio_image_fits='d:\\ecdfs\\atlas_cdfs_map.fits'
   nonradio_image_fits='d:\\ecdfs\\swire_cdfs_i1.fits'
else:
   survey='atlas'
   field='elais'
   swire_schema='swire_es1'
   radio_image_fits='d:\\elais\\atlas_elaiss1_map.fits'
   nonradio_image_fits='d:\\elais\\elais_s1_factor2.fits'
   
print "Field : ",field," ; swire_schema : ",swire_schema


print "Starting Postage Stamps"

# Connect to the local database with the atlas uid

db=_mysql.connect(host="localhost",user="atlas",passwd="atlas")

# Lets run a querry
# This gets a list of the possible radio pairs  

sql1="select t1.cid,t1.swire_index_spitzer,t2.swire_index_spitzer,t1.lr,t1.reliability,t2.r_arcsec \
      from atlas."+field+"_matches t1, atlas.nn_"+field+"_matches t2 \
      where t1.cid=t2.cid \
      and t1.swire_index_spitzer != t2.swire_index_spitzer \
      and t1.reliability > 0.8;"
 
print sql1
db.query(sql1)

# store_result() returns the entire result set to the client immediately.
# The other is to use use_result(), which keeps the result set in the server
#and sends it row-by-row when you fetch.

#r=db.store_result()
# ...or...
r1=db.use_result()

# fetch results, returning char we need float !

rows1=r1.fetch_row(maxrows=0)

# Close connection to the database
db.close()

#print rows

for row1 in rows1:
    cid1=row1[0]
    swire_id_lr=row1[1]
    swire_id_nn=row1[2]
    lr_reliability=row1[3]
    nn_r_arcsec=row1[4]
    print "Match :",cid1,swire_id_lr,swire_id_nn,lr_reliability,nn_r_arcsec

#   We need the Radio Source ra & dec
#   Connect to the local database with the atlas uid

    db=_mysql.connect(host="localhost",user="atlas",passwd="atlas")
    
    sql2="select ra,declination \
          from "+survey+".dr3_"+field+"_cmpcat \
          where id='"+cid1+"';"
    print sql2
    db.query(sql2)

    r2=db.use_result()
    sub_rows2=r2.fetch_row(maxrows=1)
    for sub_row2 in sub_rows2:
        ra_radio=sub_row2[0]
        dec_radio=sub_row2[1]
# Close connection to the database
    db.close()
    
    f_ra_radio1=float(ra_radio)
    f_dec_radio1=float(dec_radio)

    print "Radio RA Dec :",ra_radio,dec_radio
    
# Connect to the local database with the atlas uid, get the ra/dec for each candidate.
# We now need the ra and dec for the LR candidate and the NN candidate
    db=_mysql.connect(host="localhost",user="atlas",passwd="atlas")

#   So now get coords for spitzer match for the LR candidate
    sql3=("SELECT ra_spitzer, dec_spitzer \
              from "+swire_schema+".swire \
              where index_spitzer="+swire_id_lr+";")
    print sql3,"\n"
    db.query(sql3)

    r3=db.use_result()
    sub_rows3=r3.fetch_row(maxrows=1)
    for sub_row3 in sub_rows3:
        ra_lr=sub_row3[0]
        dec_lr=sub_row3[1]
# Close connection to the database
    db.close()
    print "LR RA Dec :",ra_lr,dec_lr
 
    
#   So now get coords for spitzer match for the NN candidate
    db=_mysql.connect(host="localhost",user="atlas",passwd="atlas")
    sql4=("SELECT ra_spitzer, dec_spitzer \
              from "+swire_schema+".swire \
              where index_spitzer="+swire_id_nn+";")
    print sql4,"\n"
    db.query(sql4)

    r4=db.use_result()
    sub_rows4=r4.fetch_row(maxrows=1)
    for sub_row4 in sub_rows4:
        ra_nn=sub_row4[0]
        dec_nn=sub_row4[1]
# Close connection to the database
    db.close()
    print "NN RA Dec :",ra_nn,dec_nn

#   Get the returned rows and print out, for each non-radio candidate
#   within the search radius of the radio source

    
#------------------------------------------------------------------------------             
#   Create a DS9 region file for this radio source

    region_file_name='d:\\'+field+'\\lr_vs_nn\\'+cid1+'.reg'
    f=open(region_file_name,'w')
    f.write('global color=blue font="helvetica 10 normal "\n')
    # put a cross for the radio source
    f.write('global color=red\n')
    f.write('fk5;circle( '+ra_radio+' , '+dec_radio+' ,1") # point=cross text={'+cid1+'}\n')
    f.write('fk5;circle( '+ra_radio+' , '+dec_radio+' ,10") # point=cross\n')
 
#   Define start coords for full txt string for object
    t_ra1=f_ra_radio1+0.02
    t_dec1=f_dec_radio1+0.01
       
#    print "Spitzer Candidate: ",swire_id,ra_spitzer,dec_spitzer

    # add lines to the region file to identify the non-radio candidates

    f.write('global color=yellow\n')
    f.write('fk5;circle( '+ra_lr+' , '+dec_lr+' ,1") # point=cross\n')
#    f.write('fk5;circle( '+ra_lr+' , '+dec_lr+' ,0.05") # text={'+str(swire_id_lr)+'}\n')
    f.write('global color=magenta\n')
    f.write('fk5;circle( '+ra_nn+' , '+dec_nn+' ,2") # point=cross\n')
#    f.write('fk5;circle( '+ra_nn+' , '+dec_nn+' ,0.05") # text={'+str(swire_id_nn)+'}\n')
    
    # put in the values of spitzer_index, relibility & likelihood
    #f.write('fk5;circle('+str(t_ra1)+' , '+str(t_dec1)+',0.1") # text={'+str(idx_sub_row)+' : '+swire_id+' '+rel+'}\n')

    # Close ihe region file
    f.close()

#------------------------------------------------------------------------------
    # Now create DS9 commands and execute

    contour_file_name='d:\\'+field+'\\lr_vs_nn\\'+cid1+'.con'
    levelsfile='d:\\'+field+'\\lr_vs_nn\\contour_ds9.lev'
    postage_stamp_filename1='d:\\'+field+'\\lr_vs_nn\\atlas_'+cid1+'.jpeg'
    postage_stamp_filename2='d:\\'+field+'\\lr_vs_nn\\atlas_swire_'+cid1+'.jpeg'
    
    cmd1='ds9 -zscale -invert '+radio_image_fits+' -crop '+ra_radio+' '+dec_radio+ \
         ' 100 100 wcs fk5 arcsec -contour open -contour loadlevels '+levelsfile+' -contour yes ' + \
         ' -regions '+region_file_name+ ' -colorbar no ' +\
         '-contour save '+contour_file_name+' -contour close -zoom to fit ' +\
         '-saveimage '+postage_stamp_filename1+' 100 -exit'
    print cmd1
 
    postage_stamp_filename='d:\\'+field+'\\lr_vs_nn'+cid1+'_'+ra_radio+'_'+dec_radio+'.jpeg'
    cmd2='ds9 -zscale -invert '+ nonradio_image_fits+' -crop '+ra_radio+' '+dec_radio+ \
         ' 100 100 wcs fk5 arcsec -contour open -contour load '+contour_file_name+ \
         ' -regions '+region_file_name+ ' -colorbar no ' +\
         ' -contour close -zoom to fit -saveimage '+postage_stamp_filename2+' 100 -exit '

    print cmd2

    os.system(cmd1)
    os.system(cmd2)


# End of do block

print "End"

